﻿using DbJobsApi.Dto;
using DbJobsApi.Dto.Mysql;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;
using DbJobsApi.Extensions;
using MySql.Data.MySqlClient;

namespace DbJobsApi.Controllers
{
    [Route("/api/[controller]/[action]")]
    [ApiController]
    public class MysqlController : ControllerBase
    {
        private readonly ILogger<SqlServerController> logger;
        public MysqlController(ILogger<SqlServerController> logger)
        {
            this.logger = logger;
        }

        /// <summary>
        /// 获取指定库的所有表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public PageResult<CustomTableEntity> GetTables(SearchTableEntity entity)
        {
            using var conn = new MySqlConnection(entity.DbConnStr);

            var param = new Dictionary<string, object>
            {
                { "@dbName", entity.DbName }
            };
            var list = conn.MySqlServerQuery<CustomTableEntity>(
                @"
SELECT 
TABLE_NAME,	
case TABLE_TYPE when 'BASE TABLE' then 'TABLE' when 'VIEW' then 'VIEW'else '?' end 'TABLE_TYPE',
CREATE_TIME 'CREATED',
TABLE_COMMENT 'COMMENTS'
FROM information_schema.tables
WHERE TABLE_SCHEMA = @dbName
union all 
select 
routine_NAME,	
routine_TYPE 'TABLE_TYPE',
CREATED,
routine_COMMENT 'COMMENTS'
from 
information_schema.routines
WHERE routine_SCHEMA = @dbName", param);

            if (list == null)
                list = new();

            var whereList = list.AsQueryable();
            if (!string.IsNullOrEmpty(entity.TABLE_NAME))
            {
                whereList = whereList.Where(m => m.TABLE_NAME.Contains(entity.TABLE_NAME));
            }

            var records = list.Count;
            var result = new PageResult<CustomTableEntity>
            {
                rows = whereList
                        .OrderBy(entity.sidx, entity.sord)
                        .Skip((entity.page - 1) * entity.rows)
                        .Take(entity.rows).ToList(),
                records = records,
                page = entity.page,
                total = records / entity.rows + (records % entity.rows != 0 ? 1 : 0)
            };
            return result;
        }


        public class _TEMP
        {
            public string name { get; set; }
            public string type { get; set; }
            public int notnull { get; set; }
            public object dflt_value { get; set; }
            public int pk { get; set; }
        }
        /// <summary>
        /// 获取指定表的结构信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        [HttpPost]
        public List<TableColumnEntity> GetTable(GetTableEntity entity)
        {
            using var conn = new MySqlConnection(entity.DbConnStr);
           
            var param = new Dictionary<string, object>
            {
                { "@dbName", entity.DbName },
                { "@tableName", entity.TABLE_NAME }
            };
            var result = conn.MySqlServerQueryDataTable($@"select 
COLUMN_NAME,
case EXTRA when 'auto_increment' then 1 else 0 end 'AUTOIN',
case COLUMN_KEY when 'PRI' then 1 else 0 end 'ISKEY',
DATA_TYPE,
CASE CHARACTER_MAXIMUM_LENGTH when null then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end 'DATA_LENGTH',
CASE DATA_TYPE when 'numeric' then numeric_scale when 'decimal' then numeric_scale else null end DATA_SCALE,
case IS_NULLABLE when 'YES' then 1 else 0 end 'NULLABLE',
column_default 'DATA_DEFAULT', 
column_COMMENT 'COMMENTS'
FROM information_schema.columns
where TABLE_SCHEMA = @dbName and table_name = @tableName
order by ORDINAL_POSITION", param);

            var list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<TableColumnEntity>>(Newtonsoft.Json.JsonConvert.SerializeObject(result));
            return list;
        }


        /// <summary>
        /// 编辑表结构定义 //此方法存在注入风险
        /// </summary>
        /// <param name="entity"></param>
        [HttpPost]
        public FlagEntity Edit(CustomTableEntity entity)
        {
            using var conn = new MySqlConnection(entity.DbConnStr);
            try
            {
                var tableInfo = GetTables(new()
                {
                    page = 1,
                    sidx = "TABLE_NAME",
                    rows = 1,
                    sord = "asc",
                    DbName = entity.DbName,
                    DbConnStr = entity.DbConnStr,
                    TABLE_NAME = string.IsNullOrEmpty(entity.EDIT_TABLE_NAME) ? entity.TABLE_NAME : entity.EDIT_TABLE_NAME
                }).rows;

                if (tableInfo == null || tableInfo.Count == 0)
                {
                    //建表
                    var colSql = entity.Columns.Select(m => m.COLUMN_NAME_SQL).ToList();
                    if (entity.Columns.Any(m => m.ISKEY == 1))
                    {
                        colSql.Add($"PRIMARY KEY ( {Environment.NewLine}" +
                            $"{string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => $"`{m.COLUMN_NAME}`"))} {Environment.NewLine}" +
                            $" ){Environment.NewLine}");
                    }
                    _ = conn.MySqlServerExcute($"CREATE TABLE `{entity.TABLE_NAME}` ( {Environment.NewLine}{string.Join(",", colSql)} {Environment.NewLine})");
                    _ = conn.MySqlServerExcute($"ALTER TABLE `{entity.TABLE_NAME}` COMMENT = '{entity.COMMENTS?.Replace("'","''")}'");
                }
                else if (tableInfo.Count == 1)
                {
                    var columns = GetTable(new() { DbConnStr = entity.DbConnStr, DbName = entity.DbName, TABLE_NAME = entity.EDIT_TABLE_NAME });

                    //修改表名
                    if (!entity.EDIT_TABLE_NAME.Equals(entity.TABLE_NAME))
                    {
                        logger.LogInformation($"表名发生变化");
                        _ = conn.MySqlServerExcute($"ALTER TABLE \"{entity.EDIT_TABLE_NAME}\" RENAME TO \"{entity.TABLE_NAME}\"");
                    }
                    //重命名的字段
                    var renames = entity.Columns.Where(m => !string.IsNullOrEmpty(m.EDIT_COLUMN_NAME) && !m.EDIT_COLUMN_NAME.Equals(m.COLUMN_NAME))
                                    .ToList();
                    if (renames.Count > 0)
                    {
                        logger.LogInformation($"修改字段名:{renames.Count}");
                        foreach (var m in renames)
                        {
                            _ = conn.MySqlServerExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" change \"{m.EDIT_COLUMN_NAME}\" {m.COLUMN_NAME_SQL}");
                        }
                    }

                    //任一属性被修改则要alter字段
                    var edits = (from a in columns
                                 join b in entity.Columns on a.COLUMN_NAME equals b.EDIT_COLUMN_NAME
                                 where a.DATA_TYPE != b.DATA_TYPE ||
                                 a.DATA_LENGTH != b.DATA_LENGTH ||
                                 a.DATA_SCALE != b.DATA_SCALE ||
                                 a.NULLABLE != b.NULLABLE ||
                                 a.COMMENTS != b.COMMENTS
                                 select $"ALTER TABLE `{entity.TABLE_NAME}` CHANGE COLUMN {b.COLUMN_NAME_SQL}")
                                .ToList();
                    if (edits.Count > 0)
                    {
                        logger.LogInformation($"修改字段属性:{edits.Count}");
                        edits.ForEach(m => conn.MySqlServerExcute(m));
                    }
                    //修改默认值
                    (from a in columns
                            join b in entity.Columns on a.COLUMN_NAME equals b.EDIT_COLUMN_NAME
                            where
                            (a.DATA_DEFAULT != null && !a.DATA_DEFAULT.Equals(b?.DATA_DEFAULT)) ||
                            (b.DATA_DEFAULT != null && !b.DATA_DEFAULT.Equals(a?.DATA_DEFAULT))
                     select (b.DATA_DEFAULT == null || string.IsNullOrEmpty(b.DATA_DEFAULT.ToString()) ?
                            $"ALTER TABLE `{entity.TABLE_NAME}` ALTER COLUMN `{b.EDIT_COLUMN_NAME}` DROP DEFAULT;"
                            :
                            $"ALTER TABLE `{entity.TABLE_NAME}` MODIFY COLUMN `{b.EDIT_COLUMN_NAME}` {b.DATA_TYPE} {(b.NULLABLE == "Y" ? "NOT NULL": "NULL")} DEFAULT {b.DATA_DEFAULT.ToString().Replace("'", "''")};"
                            )
                            )
                        .ToList()
                        .ForEach(m => conn.MySqlServerExcute(m));


                    //新结构不存在则删除字段
                    var dels = (from a in columns
                                join b in entity.Columns on a.COLUMN_NAME equals b.EDIT_COLUMN_NAME into bb
                                from bbb in bb.DefaultIfEmpty()
                                where bbb == null
                                select a.COLUMN_NAME)
                               .Select(m => $"ALTER TABLE `{entity.TABLE_NAME}` DROP COLUMN `{m}`")
                               .ToList();
                    if (dels.Count > 0)
                    {
                        logger.LogInformation($"删除字段:{dels.Count}");
                        foreach (var del in dels)
                        {
                            _ = conn.MySqlServerExcute(del);
                        }
                    }

                    //结构没有原始列名则是新增
                    var adds = entity.Columns.Where(m => string.IsNullOrEmpty(m.EDIT_COLUMN_NAME))
                                    .Select(m => $"ALTER TABLE `{entity.TABLE_NAME}` ADD {m.COLUMN_NAME_SQL}")
                                    .ToList();
                    if (adds.Count > 0)
                    {
                        logger.LogInformation($"新增字段:{adds.Count}");
                        foreach (var add in adds)
                        {
                            _ = conn.MySqlServerExcute(add);
                        }
                    }

                    //主键变化
                    var keyChange = !string.Join(",", columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME)).Equals(
                                string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME))
                                );
                    if (keyChange)
                    {
                        logger.LogInformation($"主键发生变化");
                        var hasKey = columns.Exists(m => m.ISKEY == 1);
                        if (hasKey)
                        {
                            _ = conn.MySqlServerExcute($"ALTER TABLE `{entity.TABLE_NAME}` DROP PRIMARY KEY");
                        }
                        var newKeys = string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME));
                        if (!string.IsNullOrEmpty(newKeys))
                        {
                            _ = conn.MySqlServerExcute($"ALTER TABLE `{entity.TABLE_NAME}` {Environment.NewLine} " +
                                $"ADD PRIMARY KEY PK_{entity.TABLE_NAME}({newKeys})");
                        }
                    }
                    if(entity.COMMENTS != tableInfo[0].COMMENTS)
                        _ = conn.MySqlServerExcute($"ALTER TABLE `{entity.TABLE_NAME}` COMMENT = '{entity.COMMENTS?.Replace("'", "''")}'");
                }
                else
                {
                    return new() { flag = false, msg = "你看到的表不是新的，请刷新列表重新编辑" };
                }
            }
            catch (Exception ex)
            {
                return new() { flag = false, msg = ex.Message };
            }
            return new() { flag = true };
        }

        [HttpPost]
        public FlagEntity GetMetaData(GetMetaEntity entity)
        {
            using var conn = new MySqlConnection(entity.DbConnStr);

            var dt = conn.MySqlServerQueryDataTable(
                @$"show create {entity.Type} {entity.Name}");

            string data = dt.Rows[0][entity.Type == "VIEW" ? 1 : 2].ToString();

            data = $"DROP {entity.Type} IF EXISTS `{entity.Name}` ;\n\nCREATE {data.Substring(data.IndexOf(entity.Type) - 1)}";
            
            return new FlagEntity
            {
                flag = true,
                data = data,
                msg = ""
            };
        }

        [HttpPost]
        public object RunSql(RunSqlEntity entity)
        {
            using var conn = new MySqlConnection(entity.DbConnStr);

            var dts = conn.MySqlServerQueryDataTable(entity.Sqls, out string[] logs);

            var tables = new List<object>();
            if (dts?.Length > 0)
            {
                foreach (var dt in dts)
                {
                    var columns = new List<object>();
                    for (int i = 0; i < dt?.Columns.Count; i++)
                    {
                        var col = dt.Columns[i];
                        columns.Add(new { name = col.ColumnName, type = col.DataType.Name });
                    }
                    tables.Add(new { columns, data = dt });
                }
            }

            return new
            {
                flag = true,
                tables,
                logs
            };
        }
    }
}
